# -*- coding: UTF-8 -*-
__author__ = 'czyigy'


import MySQLdb as mdb
from MySqlPool import Mysql

import traceback
from datetime import timedelta, datetime as dt_date_time
from logger import logger
from constant import *
import copy
import sys
reload(sys)
sys.setdefaultencoding('utf8')


def expt_raise(inst,firstName,secondName):
    """throw execption"""
    if inst is not None:
        logger.warning('%s',str(type(inst)))
        logger.warning('%s',str(type(inst.args)))
        logger.warning('%s',str(inst))

    if firstName is not None:
       raise Exception(firstName, secondName)

class MysqlDb:
    """wrap the function of mysqldb"""
    def __init__(self):
        self.SystemInfo = {
            'voltage': 0,
            'electricity': 0,
            'temperate': 0,
            }

        self.LampInfo ={
            'voltage': 0,
            'electricity': 0,
            'temperate': 0,
            'brightness_per': 0,
            'status': 0,
            'power': 0,
            }

        self.SolarInfo = {
            'voltage': 0,
            'electricity': 0,
            'power': 0,
            }

        self.BatteryInfo ={
            'status': 0,
            'voltage': 0,
            'charge_electricity': 0,
            'discharge_electricity': 0,
            'charge_power': 0,
            'discharge_power': 0,
            'temperate': 0,
            'all_electric_quantity': 0,
            'surplus_electric_quantity': 0,
            'soc': 0,
            'charge_electric_quantity': 0,
            'discharge_electric_quantity': 0,
            'charge_max_power': 0,
            'discharge_max_power': 0,
            'charge_min_electricity': 0,
            'charge_max_electricity': 0,
            'discharge_min_electricity': 0,
            'discharge_max_electricity': 0,
            'min_temperate': 0,
            'max_temperate': 0,
            'battery_min_voltage': 0,
            'battery_max_voltage': 0,
            }

        self.DailyEQInfo = {
            'electric_quantity_production': 0,
            'electric_quantity_consumption': 0,
            }

        self.ProductInfo ={
            'product_type': '',
            'product_software_version': '',
            'product_hardware_version': '',
            'product_sequence': '',
            }

        self.AlarmInfo = {
            'alarm_type': 0,
            }

        self.HistoryInfo = {
            'run_days': 0,
            'battery_over_discharge_times': 0,
            'battery_full_charge_times': 0,
            'battery_charge_electric_quantity': 0,
            'battery_discharge_electric_quantity': 0,
        }

        self.Flag = 3
        self.MAC = 0
        self.VerifyCode = 0
        self.PDU = 0
        self.DataLength = 0

        self.all_data = {
            'MAC':self.MAC,
            'flag':self.Flag,
            'VerifyCode':self.VerifyCode,
            'PDU':self.PDU,
            'DataLength':self.DataLength,
            'HistoryInfo':self.HistoryInfo,
            'AlarmInfo':self.AlarmInfo,
            'ProductInfo':self.ProductInfo,
            'DailyEQInfo':self.DailyEQInfo,
            'BatteryInfo':self.BatteryInfo,
            'SolarInfo':self.SolarInfo,
            'LampInfo':self.LampInfo,
            'SolarInfo':self.SolarInfo,
            'SystemInfo':self.SystemInfo
        }
        self.lamp_db = Mysql()
    # conn = None

    def insertOne(self, jsobj):
        try:
            tblName = 'lamplog'
            if tblName is None:
                return False
            #save to db
            self.all_data = jsobj
            self.SystemInfo = self.all_data['SystemInfo']
            self.LampInfo = self.all_data['LampInfo']
            self.HistoryInfo = self.all_data['HistoryInfo']
            self.AlarmInfo = self.all_data['AlarmInfo']
            self.BatteryInfo = self.all_data['BatteryInfo']
            self.SolarInfo = self.all_data['SolarInfo']

            sql = "insert into " + tblName + "(address, sysvoltage, syscurrent, temper,"\
                    "lighteness, lampstatus, lampvoltage, lampcurrent, lamppower," \
                    "boardvoltage,  boardcurrent, boardpower," \
                    " battstatus,chargestage,battvoltage,batttemper,chargevoltage,chargecurrent," \
                    "chargepower,discharvoltage,discharcurrent,dischargepower," \
                    "electrictotal,electricleft,daychargeah,daydischarah,daychargemaxpow," \
                    "daydischarmaxpow,updatetime) values("\
                    + str(self.all_data['MAC']) + "," + str(self.SystemInfo['voltage']) + ","\
                    +str(self.SystemInfo['electricity']) + "," +str(self.LampInfo['temperate'])+ "," \
                    +str(self.LampInfo['brightness_per']) + "," +str(self.LampInfo['status'])+ "," \
                    + str(self.LampInfo['voltage'])+ "," + str(self.LampInfo['electricity'])+ "," \
                    + str(self.LampInfo['power'])+ "," + str(self.SolarInfo['voltage'])+ "," \
                    + str(self.SolarInfo['electricity'])+","+ str(self.SolarInfo['power']) + ","\
                    + str(self.BatteryInfo['status']) + "," + str(self.BatteryInfo['charge_status']) + ","+ str(self.BatteryInfo['voltage']) + "," \
                    + str(self.BatteryInfo['temperate']) + "," + str(self.BatteryInfo['voltage']) + "," \
                    + str(self.BatteryInfo['charge_electricity']) + "," + str(self.BatteryInfo['charge_power']) + "," \
                    + str(self.BatteryInfo['voltage']) + "," + str(self.BatteryInfo['discharge_electricity']) + "," \
                    + str(self.BatteryInfo['discharge_power']) + "," + str(self.BatteryInfo['all_electric_quantity']) + "," \
                    + str(self.BatteryInfo['surplus_electric_quantity']) + "," + str(self.BatteryInfo['charge_electric_quantity']) + "," \
                    + str(self.BatteryInfo['discharge_electric_quantity']) + "," + str(self.BatteryInfo['charge_max_power']) + "," \
                    + str(self.BatteryInfo['discharge_max_power']) + ", '" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "')"
            self.lamp_db.insertOne(sql)
            return True
        except Exception as inst:
             expt_raise(inst,None,"insertOne")
             return False

    def insert_system_info(self,info_list):
        if len(info_list) == 0:
            return
        tblName = 'system_info_log'
        sql_head = "insert into " + tblName + "(lampid, sysvoltage, syscurrent, temper,updatetime) values "
        values = ''
        for item in info_list:
            values += "("+ str(item['lampid']) + "," + str(item['voltage']) + ","+str(item['electricity']) + "," \
                      + str(item['temperate']) + ", '" + item["time"]+ "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:

                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
        return True

    def insert_lamp_info(self,info_list):
        if len(info_list) == 0:
            return True
        tblName = 'lamp_info_log'
        sql_head = "insert into " + tblName + "(lampid, lighteness, lampstatus, electric_status, lampvoltage," \
                "lampcurrent, lamppower, temper,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + "," + str(item['brightness_per']) + "," + str(item['status']) + "," \
                      + str(item['electric_status']) + "," + str(item['voltage']) + "," + str(item['electricity']) + "," \
                    + str(item['power']) + "," + str(item['temperate']) + ", '" + item["time"] + "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def update_lamp_info(self, info_list):
        try:
            values_list = []
            for item in info_list:
                value = (item['brightness_per'], item['status'], item['lampid'])
                values_list.append(value)
            if len(values_list) > 0:
                lampinfosql = "update lampinfo set lighteness = %s, status = %s, devstatus = 0 where id = %s"
                self.lamp_db.updateMany(lampinfosql, values_list)
            return True
        except:
            logger.error(traceback.format_exc())
            return False

    def insert_history_info(self,info_list):
        if len(info_list) == 0:
            return True
        today = dt_date_time.today()
        try:
            for item_h in info_list:
                charge_ah_sql_h = "select lampid, totalchargeah, totaldischarah from history_info_log where lampid = %s order by updatetime desc limit 1" % (
                    item_h['lampid'])

                total_ah_infos = self.lamp_db.getAll(charge_ah_sql_h)
                totalchargeah = 0
                totaldischarah = 0
                if len(total_ah_infos) > 0:
                    totalchargeah = total_ah_infos[0]['totalchargeah']
                    totaldischarah = total_ah_infos[0]['totaldischarah']
                item_h['totalchargeah'] = item_h['totalchargeah'] + totalchargeah
                item_h['totaldischarah'] = item_h['totaldischarah'] + totaldischarah
        except:
            pass
        tblName = 'history_info_log'
        sql_head = "insert into " + tblName + "(lampid, rundays, overtimes, fulltimes,totalchargeah, totaldischarah,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + "," + str(item['run_days']) + "," +str(item['battery_over_discharge_times'])+\
                      "," +str(item['battery_full_charge_times'])+ "," +str(item['totalchargeah'])\
                      + "," +str(item['totaldischarah'])+", '" + item["time"]+ "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def insert_battery_info(self,info_list):
        if len(info_list) == 0:
            return True
        tblName = 'battery_info_log'
        sql_head = "insert into " + tblName + "(lampid, battstatus, chargestage, battvoltage," \
                                              "chargecurrent, discharcurrent,chargepower,dischargepower," \
                                              "batttemper,electrictotal,electricleft,electricSOC," \
                                              "voltagedaymin,voltagedaymax,daychargeah,daydischarah," \
                                              "daychargemaxpow,daydischarmaxpow,daychargemincurrent," \
                                              "daycharmaxcurrent,daydischargemincurrent,daydischarmaxcurrent," \
                                              "daybattmintemper,daybattmaxtemper,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + "," + str(item['status']) + ","+ str(item['charge_status']) + ","+ str(item['voltage']) +\
                        ","  +str(item['charge_electricity'])+ "," +str(item['discharge_electricity']) +\
                        ","  +str(item['charge_power'])+ "," +str(item['discharge_power']) +\
                        ","  +str(item['temperate'])+ "," +str(item['all_electric_quantity']) +\
                        ","  +str(item['surplus_electric_quantity'])+ "," +str(item['soc']) +\
                    ","  +str(item['battery_min_voltage'])+ "," +str(item['battery_max_voltage']) +\
                    ","  +str(item['charge_electric_quantity'])+ "," +str(item['discharge_electric_quantity']) +\
                    ","  +str(item['charge_max_power'])+ "," +str(item['discharge_max_power']) +\
                    ","  +str(item['charge_min_electricity'])+ "," +str(item['charge_max_electricity']) +\
                    ","  +str(item['discharge_min_electricity'])+ "," +str(item['discharge_max_electricity']) +\
                    ","  +str(item['min_temperate'])+ "," +str(item['max_temperate'])+", '" + item["time"]+ "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                #logger.error(sql)
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def insert_solar_info(self,info_list):
        if len(info_list) == 0:
            return True
        tblName = 'solar_info_log'
        sql_head = "insert into " + tblName + "(lampid, solarvoltage, solarcurrent, solarpower,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + "," + str(item['voltage']) + "," +str(item['electricity'])+ ","\
                      + str(item['power'])+ ", '"+ item["time"]+ "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def insert_alarm_info(self,info_list):
        if len(info_list) == 0:
            return True
        for info in info_list:
            if self.update_lamp_alarm_status(info) == False:
                self.update_lamp_alarm_status(info)
        tblName = 'alarm_info_log'
        #sql_head = "SET NAMES utf8;insert into " + tblName + "(lampid, alarmtype, stralarmtype, updatetime) values "
        sql_head = "insert into " + tblName + "(lampid, alarmtype, stralarmtype, updatetime) values "
        values = ''
        for item in info_list:
          if str(item['alarm_type']) == "0":
            continue
          # if str(item['alarm_type']) == "6":
            # logger.info("abcd333")
            # continue
          # if str(item['alarm_type']) == "28":
            # logger.info("abcd222")
            # continue
          values += "(" + str(item['lampid']) + "," + str(item['alarm_type']) + ",'" + item['str_alarm_type'].encode('utf-8')+ "', '" + item["time"] + "'),"
          #logger.info("abc%s"% values)
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def update_lamp_alarm_status(self,alarm_info):
        try:
            isfaulted = 1
            is_add_fault = 0
            if alarm_info['alarm_type'] == 0:
                isfaulted = 0
            select_lamp_sql = 'select projectid, networkid, isfaulted from lampinfo where id = %s' % alarm_info['lampid']

            lamp_sql = "update lampinfo set isfaulted = %s where id = %s" % (isfaulted,alarm_info['lampid'])
            lampinfo = self.lamp_db.getAll(select_lamp_sql)
            if len(lampinfo) > 0:
                if lampinfo[0]['isfaulted'] == 1 and isfaulted == 0:
                    is_add_fault = -1
                    self.lamp_db.update(lamp_sql)
                elif lampinfo[0]['isfaulted'] == 0 and isfaulted == 1:
                    is_add_fault = 1
                    self.lamp_db.update(lamp_sql)
            else:
                return
            lamp_projectid = lampinfo[0]['projectid']
            lamp_networkid = lampinfo[0]['networkid']

            select_project_sql = 'select  lampcount, faultcount from project where id = %s' % lamp_projectid
            select_network_sql = 'select  lampcount, faultcount from network where id = %s' % lamp_networkid
            projectinfo = self.lamp_db.getAll(select_project_sql)
            networkinfo = self.lamp_db.getAll(select_network_sql)
            if len(projectinfo) > 0:
                faultcount = projectinfo[0]['faultcount']
                faultcount += is_add_fault
                if faultcount > projectinfo[0]['lampcount']:
                    faultcount = projectinfo[0]['lampcount']
                elif faultcount < 0:
                    faultcount = 0
                if projectinfo[0]['faultcount'] != faultcount:
                    project_sql = "update project set faultcount = %s where id = %s" % (faultcount, lamp_projectid)
                    self.lamp_db.update(project_sql)
                    logger.error("alarm_update projectid %s" % lamp_projectid)
            if len(networkinfo) > 0:
                netfaultcount = networkinfo[0]['faultcount']
                netfaultcount += is_add_fault
                if netfaultcount > networkinfo[0]['lampcount']:
                    netfaultcount = networkinfo[0]['lampcount']
                elif netfaultcount < 0:
                    netfaultcount = 0
                if networkinfo[0]['faultcount'] != netfaultcount:
                    network_sql = "update network set faultcount = %s where id = %s" % (netfaultcount, lamp_networkid)
                    self.lamp_db.update(network_sql)
                    logger.error("alarm_update network_id %s" % lamp_networkid)

            result = True
        except:
            logger.error(traceback.format_exc())
            result = False
        return result

    def insert_electric_info(self,info_list):
        if len(info_list) == 0:
            return True
        tblName = 'electric_info_log'
        sql_head = "insert into " + tblName + "(lampid, daygeneration, dayconsumption, totalgeneration,totalconsumption,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + "," + str(item['electric_quantity_production']) + "," +str(item['electric_quantity_consumption'])+\
                      "," + str(item['total_electric_quantity_production'])+ "," + str(item['total_electric_quantity_consumption'])+", '"+ item["time"]+ "'),"
        if values.endswith(","):
            values = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True

    def insert_product_info(self,info_list):
        if len(info_list) == 0:
            return True
        tblName = 'product_info_log'
        sql_head = "insert into " + tblName + "(lampid, productmodel, softwareversion, hardwareversion,productsequence,updatetime) values "
        values = ''
        for item in info_list:
            values += "(" + str(item['lampid']) + ",'" + str(item['product_type']) + "','" +str(item['product_software_version'])+\
                      "','" + str(item['product_hardware_version'])+ "','" + str(item['product_sequence'])+"', '"+ item["time"]+ "'),"
        if values.endswith(","):
            values = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        return True


    def insertMany(self, result_list):
        system_info_list =[]
        lamp_info_list = []
        history_info_list =[]
        alarm_info_list = []
        battery_info_list = []
        solar_info_list = []
        product_info_list = []
        electric_info_list = []

        #if not result_list:
	    #    return
        #logger.info("save info :%s", str(result_list))
        for item in result_list:
            if type(item['lamp_id']) != long:
                logger.error(item['lamp_id'])
                continue
            public_elment = {"lampid": item['lamp_id'],
                             'projectid': item['projectid'],
                             'networkid': item['networkid'],
                             "time": item['time']}
            sql = "select updatetime from lamp_info_log where lampid = %s order by updatetime desc limit 1" %(item['lamp_id'])
            try:

                info_list = self.lamp_db.getAll(sql)
                if len(info_list) > 0:
                    update_time = info_list[0]['updatetime']

                    newupdatetime = dt_date_time.strptime(item['time'],'%Y-%m-%d %H:%M:%S')
                    delta = newupdatetime - update_time
                    if delta < timedelta(minutes=1):
                        logger.info("old time %s"%str(update_time))
                        logger.info("not save data, have update less than 1 minutes %d"%item['lamp_id'])
                        continue
                self.patrol_rules_updatetime(item['lamp_id'])
            except:
                logger.error(traceback.format_exc())
                return
            PDU = item["PDU"]
            if PDU == 70:
                system_info = copy.deepcopy(item['SystemInfo'])
                system_info.update(public_elment)
                system_info_list.append(system_info)

                lamp_info = copy.deepcopy(item['LampInfo'])
                lamp_info.update(public_elment)
                lamp_info_list.append(lamp_info)

                battery_info = copy.deepcopy(item['BatteryInfo'])
                battery_info.update(public_elment)
                battery_info_list.append(battery_info)

                solar_info = copy.deepcopy(item['SolarInfo'])
                solar_info.update(public_elment)
                solar_info_list.append(solar_info)

                electric_info = copy.deepcopy(item['DailyEQInfo'])
                electric_info.update(public_elment)
                electric_info_list.append(electric_info)

                history_info = copy.deepcopy(item['HistoryInfo'])
                history_info.update(public_elment)
                history_info_list.append(history_info)

                alarm_info = copy.deepcopy(item['AlarmInfo'])
                alarm_info.update(public_elment)
                alarm_info_list.append(alarm_info)
            elif PDU == 0:
                system_info = copy.deepcopy(item['SystemInfo'])
                system_info.update(public_elment)
                system_info_list.append(system_info)
            elif PDU == 3:
                lamp_info = copy.deepcopy(item['LampInfo'])
                lamp_info.update(public_elment)
                lamp_info_list.append(lamp_info)
            elif PDU == 9:
                solar_info = copy.deepcopy(item['SolarInfo'])
                solar_info.update(public_elment)
                solar_info_list.append(solar_info)
            elif PDU == 13:
                battery_info = copy.deepcopy(item['BatteryInfo'])
                battery_info.update(public_elment)
                battery_info_list.append(battery_info)
            elif PDU == 48:
                electric_info = copy.deepcopy(item['DailyEQInfo'])
                electric_info.update(public_elment)
                electric_info_list.append(electric_info)
            elif PDU == 60:
                history_info = copy.deepcopy(item['HistoryInfo'])
                history_info.update(public_elment)
                history_info_list.append(history_info)
            elif PDU == 66:
                alarm_info = copy.deepcopy(item['AlarmInfo'])
                alarm_info.update(public_elment)
                alarm_info_list.append(alarm_info)
            elif PDU == 512:
                product_info = copy.deepcopy(item['ProductInfo'])
                product_info.update(public_elment)
                product_info_list.append(product_info)
        if not self.insert_alarm_info(alarm_info_list):
            self.insert_alarm_info(alarm_info_list)
        if not self.insert_battery_info(battery_info_list):
            self.insert_battery_info(battery_info_list)
        if not self.insert_electric_info(electric_info_list):
            self.insert_electric_info(electric_info_list)
        if not self.insert_history_info(history_info_list):
            self.insert_history_info(history_info_list)
        if not self.insert_lamp_info(lamp_info_list):
            self.insert_lamp_info(lamp_info_list)
        if not self.update_lamp_info(lamp_info_list):
            self.update_lamp_info(lamp_info_list)
        if not self.insert_solar_info(solar_info_list):
            self.insert_solar_info(solar_info_list)
        if not self.insert_product_info(product_info_list):
            self.insert_product_info(product_info_list)
        if not self.insert_system_info(system_info_list):
            self.insert_system_info(system_info_list)


    def readLampInfo(self):
        sql = "select lamp.id as id, lamp.address as address, project.projectid as projectid, network.networkid as networkid from " \
              "(select id, address, projectid,networkid from lampinfo where networkid in (select id from network where status = 1)) lamp " \
              " left join (select id,projectid from project ) project on lamp.projectid = project.id" \
              " left join (select id,networkid from network) network on lamp.networkid = network.id;"
        lampinfo_dict = {}
        try:
            info_list = self.lamp_db.getAll(sql)
            #logger.info(str(info_list))
            for item in info_list:
                lampinfo = {
                    'id':item['id'],
                    'projectid':item['projectid'],
                    'networkid':item['networkid'],
                }
                #item['address'] = self.address_string_to_int(item['address'])
                item['address'] = str(item['address']).lower()
                lampinfo_dict[item['address']] = lampinfo

        except:
            logger.error(traceback.format_exc())
            return False,{}
        return True,lampinfo_dict

    def address_string_to_int(self,address):
        a_length = len(address)
        #logger.info(address)
        iaddress = 0
        try:
            for i in range(0,a_length):
                if address[a_length -i-1] in ['a','A',b','B',c','C','d','D','e','E','f','F']:
                    iaddress += self.get_address_item_value(address[a_length -i-1],i)
                elif address[a_length -i-1] in ['1','2','3','4','5','6','7','8','9','0']:
                    iaddress += int(address[a_length -i-1])*(16**i)
                else:
                    iaddress = 0
                    break
        except:
            logger.error(traceback.format_exc())
            iaddress = 0
        return iaddress

    def get_address_item_value(self,item,pos):
        value = 0
        if item =='a' or item == 'A':
            value = 10*(16**pos)
        elif item =='b' or item == 'B':
            value = 11*(16**pos)
        elif item =='c' or item == 'C':
            value = 12*(16**pos)
        elif item =='d' or item == 'D':
            value = 13*(16**pos)
        elif item =='e' or item == 'E':
            value = 14*(16**pos)
        elif item =='f' or item == 'F':
            value = 15*(16**pos)
        return value

    def get_switch_cmd(self,pdu,id):
        sql = "select * from switch_cmd where id = %s" % id
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            switch_cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':6,
                'pdu':pdu,
                'value':[item['statuscmd']]
            }
            cmd_list.append(switch_cmd)
        return True,cmd_list

    def get_dim_cmd(self,pdu,id):
        sql = "select * from dim_cmd where id = %s" % id
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            istatus = item['istatus']
            dimpercent = item['dimpercent']
            if istatus == 1:
                dimpercent = dimpercent|128
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'pdu':pdu,
                'code':6,
                'value':[dimpercent]
            }
            cmd_list.append(cmd)
        return True,cmd_list

    def get_batch_switch_cmd(self,pdu,id):
        sql = "select * from batch_switch_cmd where id = %s" % id
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            switch_cmd ={
                'cmdtype':item['cmdtype'],
                'relateid':item['relateid'],
                'code':6,
                'pdu':pdu,
                'value':[item['statuscmd']]
            }
            cmd_list.append(switch_cmd)
        return True,cmd_list

    def get_batch_dim_cmd(self,pdu,id):
        sql = "select * from batch_dim_cmd where id = %s" % id
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            istatus = item['istatus']
            dimpercent = item['dimpercent']
            if istatus == 1:
                dimpercent = dimpercent|128

            cmd ={
                'cmdtype':item['cmdtype'],
                'relateid':item['relateid'],
                'pdu':pdu,
                'code':6,
                'value':[dimpercent]
            }
            cmd_list.append(cmd)

        return True,cmd_list

    def select_electric_supply_cmd(self, cmd_type, relateid, value):
        cmd_list = []

        cmd ={
            'cmdtype': cmd_type,
            'relateid': relateid,
            'pdu': 0x305,
            'code': 6,
            'value': [value]
        }
        cmd_list.append(cmd)

        return True, cmd_list

    def get_batch_update_cmd(self,pdu,id):
        sql = "select * from batch_update_cmd where id = %s" % id
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False, []
        cmd_list = []
        for item in info_list:
            cmd ={
                'cmdtype': item['cmdtype'],
                'relateid': item['relateid'],
                'pdu': pdu,
                'code': -1,
                'value': []
            }
            cmd_list.append(cmd)
        self.update_batch_update_cmd_status(id, 0)
        return True, cmd_list

    def get_lamp_group_cmd(self, pdu, id):
        sql = "select lampid, actiontype, value from lamp_group where id = %s" % id
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False, []
        cmd_list = []
        for item in info_list:
            cmd ={
                'cmdtype': 9,
                'lampids': item['lampid'],
                'actiontype': item['actiontype'],
                'value': item['value']
            }
            cmd_list.append(cmd)
        self.update_batch_update_cmd_status(id,0)
        return True, cmd_list

    def change_rf_channel_cmd(self,pdu,id):
        sql = "select channel, effectivetime from network where id = %s" % id
        info_list = []
        cmd_list = []
        try:
            info_list = self.lamp_db.getAll(sql)

            channel_info = info_list[0] if len(info_list) > 0 else {}

            if channel_info:
                cmd = {
                    'cmdtype': 1,
                    'relateid': id,
                    'code': 16,
                    'pdu': pdu,
                    'value': [channel_info['channel'], channel_info['effectivetime']]
                }
                cmd_list.append(cmd)
            return True, cmd_list
        except:
            logger.error(traceback.format_exc())
            return False,[]




    def update_batch_update_cmd_status(self,id,istatus):
        sql = "update batch_update_cmd set statuscmd = {istatus} " \
              "where id = {id}".format(istatus = istatus,id = id)
        info_list = []

        try:
            self.lamp_db.update(sql)
        except:
            logger.error(traceback.format_exc())
            return False
        return True

    def get_patrol_cmd(self,pdu,id):
        sqlAll = "select * from patrol_cmd where id = %s" % id
        info_list = []
        try:

            info_list = self.lamp_db.getAll(sqlAll)
        except:
            logger.error(traceback.format_exc())

            return False,[]
        cmd_list = []
        for item in info_list:
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':16,
                'pdu':pdu,
                'value':[item['patroltype'],item['patrolinterval']]
            }
            cmd_list.append(cmd)

        return True,cmd_list

    def get_battery_cmd(self,pdu,id):
        sql = "select * from battery_cmd where id = %s" % id
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            if int(item['batterytype']) == 1:

                cmd ={
                    'cmdtype':0,
                    'relateid':item['id'],
                    'code':16,
                    'pdu':pdu,
                    'value':[item['capacity'],int(item['vovervoltage']*10),int(item['vlimitedcharge']*10),
                         int(item['vbalancecharge']*10),int(item['vpromotecharge']*10),int(item['vfloatingcharge']*10),
                         int(item['vpromoterecover']*10),int(item['voverdischargerecover']*10),int(item['vundervoltagewarn']*10),
                         int(item['voverdischarge']*10),int(item['balancechargetime']),int(item['promotechargetime']),
                         int(item['balanceinterval']),int(item['tempcompensation']),int(item['tempcompmax']),int(item['tempcompmin']),
                         int(item['turnfloatingcurrent']*100),int(item['batterytype']),int(item['vsystem']),int(item['minusnocharge']),int(item['chargemode'])]}
            else:
                cmd ={
                    'cmdtype':0,
                    'relateid':item['id'],
                    'code':16,
                    'pdu':pdu,
                    'value':[item['capacity'],int(item['vovervoltage']*10),int(item['vlimitedcharge']*10),
                         int(item['vbalancecharge']*10),int(item['vpromotecharge']*10),int(item['vfloatingcharge']*10),
                         int(item['vpromoterecover']*10),int(item['voverdischargerecover']*10),int(item['vundervoltagewarn']*10),
                         int(item['voverdischarge']*10),int(item['balancechargetime']),int(item['promotechargetime']),
                         int(item['balanceinterval']),int(item['tempcompensation']),int(item['tempcompmax']),int(item['tempcompmin']),
                         int(item['turnfloatingcurrent']*100),0,0,0,0]}
            cmd_list.append(cmd)
        return True,cmd_list

    def get_battery_setting_cmd(self,pdu,id):

        cmd_list = []
        cmd ={
            'cmdtype':0,
            'relateid':id,
            'code':3,
            'pdu':pdu,
            'value':0}
        cmd_list.append(cmd)
        return True,cmd_list

    def get_load_setting_cmd(self,pdu,id):

        cmd_list = []
        cmd ={
            'cmdtype':0,
            'relateid':id,
            'code':3,
            'pdu':pdu,
            'value':0}
        cmd_list.append(cmd)
        return True,cmd_list

    def get_load_param_cmd(self,pdu,id):
        sql = "select * from load_param_cmd where id = %s" % id
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':16,
                'pdu':pdu,
                'value':[item['workmode'],int(item['worktimefirst']),int(item['workpowerfirst']),
                         int(item['worktimesencond']),int(item['workpowersencond']),int(item['worktimethird']),
                         int(item['workpowerthird']),int(item['worktimeforth']),int(item['workpowerforth']),
                         int(item['voptically']*10),int(item['delaytime']),int(item['ledloadcurrent']),
                         int(item['powercmd']),int(item['switchfeature'])]
            }
            cmd_list.append(cmd)

        return True,cmd_list

    def get_policy_cmd(self,pdu,id):
        sql = "select * from policy_cmd where id = %s" % id
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        for item in info_list:
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':16,
                'pdu':pdu,
                'value':[item['policy1time'],int(item['policy1bright']),int(item['policy2time']),
                         int(item['policy2bright']),int(item['policy3time']),int(item['policy3bright']),
                         int(item['policy4time']),int(item['policy4bright']),int(item['policy5time']),
                         int(item['policy5bright']),int(item['policy6time']),int(item['policy6bright'])]
            }
            cmd_list.append(cmd)
        return True,cmd_list

    def get_lamp_address_info(self, id):
        sql = "select lampinfo.address, lampinfo.networkid, network.protocoltype from lampinfo, network where lampinfo.id = %s and lampinfo.networkid = network.id" % id
        info_list = []
        address ={}
        try:
            info_list = self.lamp_db.getAll(sql)
            #logger.info(str(info_list))
            address = info_list[0] if len(info_list) > 0 else {}
            if address:
                #address['address'] = self.address_string_to_int(address['address'])
                address['address'] = str(address['address']).lower()
        except:
            logger.error(traceback.format_exc())
            return False,{}
        return True,address

    def get_netid_from_nework(self, networikid):
        networkinfo ={}
        try:
            sql = "select networkid from network where id = %d" % networikid
            info_list = self.lamp_db.getAll(sql)
            networkinfo = info_list[0] if len(info_list) > 0 else {}
            if len(networkinfo) > 0:
                return True, networkinfo['networkid']
        except:
            logger.error(traceback.format_exc())
            return False, 0
        return False, 0



    def get_network_info_with_sn(self, deviceSN):
        networkinfo ={}
        try:

            sql = "select network.id as networkid, projectid, networkname, protocoltype, " \
                  "patrolinterval, devicetype, gatewaytype from network where network.devicesn = '%s'" % deviceSN
            info_list = self.lamp_db.getAll(sql)
            networkinfo = info_list[0] if len(info_list) > 0 else {}
            if len(networkinfo) > 0:
                if networkinfo['gatewaytype'] == 'direct':

                    if networkinfo['devicetype'] == 0:
                        sql = "select id from lampinfo where lampinfo.networkid = %d limit 1" % networkinfo['networkid']
                    elif networkinfo['devicetype'] == 1:
                        sql = "select id from envmonitor where envmonitor.networkid = %d limit 1" % networkinfo['networkid']
                    elif networkinfo['devicetype'] == 2:
                        sql = "select id from street_lampinfo where street_lampinfo.networkid = %d limit 1" % networkinfo['networkid']
                    else:
                        return False, networkinfo
                    info_list = self.lamp_db.getAll(sql)
                    devinfo = info_list[0] if len(info_list) > 0 else {}
                    if len(devinfo) > 0:
                        networkinfo['devid'] = devinfo['id']
                        return True, networkinfo
                    else:
                        return False, networkinfo
                else:
                    networkinfo['devid'] = 0
                return True, networkinfo
        except:
            logger.error(traceback.format_exc())
            return False, networkinfo
        return False, networkinfo

    def get_device_info_with_networkid(self, networkid):

        try:

            sql = "select id from envmonitor where envmonitor.networkid = %d limit 1" % networkid

            info_list = self.lamp_db.getAll(sql)
            devinfo = info_list[0] if len(info_list) > 0 else {}
            if len(devinfo) > 0:
                return True, devinfo['id']
        except:
            logger.error(traceback.format_exc())
            return False, 0
        return False, 0

    def update_network_simid(self, networkid, simid):
        lamp_sql = "update network set simid = '%s' where id = %s" % (simid, networkid)
        try:
            self.lamp_db.update(lamp_sql)
            return True
        except:
            logger.error(lamp_sql)
            logger.error(traceback.format_exc())
            return False,
    """
    def get_device_info_with_sn(self, addr, deviceSN, simid):
        lampinfo ={}
        try:
            sql = "select id as lampid, projectid, networkid, deviceid, patrolinterval from lampinfo where devicesn = '%s'" % deviceSN
            info_list = self.lamp_db.getAll(sql)
            lampinfo = info_list[0] if len(info_list) > 0 else {}
            if len(lampinfo) > 0:
                if lampinfo['deviceid'] != addr:
                    lamp_sql = "update lampinfo set deviceid = '%s' where id = %s" % (addr, lampinfo['lampid'])
                    self.lamp_db.update(lamp_sql)
                return True, lampinfo
            else:
                '''
                response = self.create_new_lamp_nbiot(addr, deviceSN, simid, NBIOT_TYPE)
                if response == True:
                    info_list = self.lamp_db.getAll(sql)
                    lampinfo = info_list[0] if len(info_list) > 0 else {}
                    if len(lampinfo) > 0:
                        return True, lampinfo
                '''
                return False, lampinfo

        except:
            logger.error(traceback.format_exc())
            return False, lampinfo
    """

    def get_network_protocoltype_by_id(self, networikid):
        try:
            sql = "select protocoltype from network where id = %d" % networikid
            info_list = self.lamp_db.getAll(sql)
            networkinfo = info_list[0] if len(info_list) > 0 else {}
            if len(networkinfo) > 0:
                return True, networkinfo['protocoltype']
        except:
            logger.error(traceback.format_exc())
            return False, 0
        return False, 0

    def get_networkinfo_list_by_projectid(self, projectid):
        try:
            sql = "select id, protocoltype from network where projectid = %d" % projectid
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) > 0:
                return True, info_list
        except:
            logger.error(traceback.format_exc())
            return False, 0
        return False, 0

    def get_lampinfo_groupby_network(self, network_id):
        sql = "select id as lampid, address from lampinfo where networkid = %s" % network_id
        address_list =[]
        try:
            info_list = self.lamp_db.getAll(sql)
            for data in info_list:
                address = data if len(info_list) > 0 else {}
                if address:
                    #address['address'] = self.address_string_to_int(address['address'])
                    address['address'] = str(address['address']).lower()
                    address_list.append(address)
        except:
            logger.error(traceback.format_exc())
            return False, []
        return True, address_list

    def get_lampinfo_groupby_networkEX(self,network_id):
        sql = "select lampinfo.id as lampid, lampinfo.address, network.protocoltype from lampinfo, network " \
              "where network.id = %s and lampinfo.networkid = network.id" % network_id
        address_list =[]
        try:
            info_list = self.lamp_db.getAll(sql)
            for data in info_list:
                address = data if len(info_list) > 0 else {}
                if address:
                    #address['address'] = self.address_string_to_int(address['address'])
                    address['address'] = str(address['address']).lower()
                    address_list.append(address)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        return True, address_list

    def get_lampinfos_by_lampid(self, lampids):
        sql = "select lampinfo.address, network.id as networkid, network.protocoltype, network.deviceid, network.status from lampinfo, network "\
              "where network.id = lampinfo.networkid and lampinfo.id in (%s)" % lampids
        info_list =[]
        try:
            info_list = self.lamp_db.getAll(sql)
            for data in info_list:
                if data:
                    #address['address'] = self.address_string_to_int(address['address'])
                    data['address'] = str(data['address']).lower()

        except:
            logger.error(traceback.format_exc())
            return False,[]
        return True, info_list

    def get_streetlampinfos_by_lampid(self, lampid):
        sql = "select street_lampinfo.address, network.id as networkid, network.protocoltype, network.deviceid, network.status from street_lampinfo, network "\
              "where network.id = street_lampinfo.networkid and street_lampinfo.id = %s" % lampid
        info_list =[]
        lampinfo = None
        try:
            info_list = self.lamp_db.getAll(sql)
            lampinfo = info_list[0] if len(info_list) > 0 else {}
            if lampinfo:
                lampinfo['address'] = str(item['address']).lower()

        except:
            logger.error(traceback.format_exc())
            return False, None
        return True, lampinfo

    def get_policycmd_by_id(self, cmdid):
        sql = "select light from policy_cmd where id = %s" % cmdid

        try:
            light = 0
            info_list = self.lamp_db.getAll(sql)
            item = info_list[0] if len(info_list) > 0 else {}
            if len(item) > 0:
                light = item['light']
                return True, light
            return False, light
        except:
            logger.error(traceback.format_exc())
            return False, 0


    def get_lampinfo_groupby_address(self, addr):
        sql = "select id, projectid, networkid from lampinfo where address = '%s'" % addr
        address_info = {}
        try:
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) > 0:
                address_info = info_list[0]
                return True, address_info
            else:
                '''
                self.create_new_lamp_for_lorawan(addr, LORA_WAN_TYPE)
                info_list = self.lamp_db.getAll(sql)

                if len(info_list) > 0:
                    address_info = info_list[0]
                    return True, address_info
                '''
                return False, address_info

        except:
            logger.error(traceback.format_exc())
            return False,address_info

    def get_lampinfo_groupby_deviceid(self, deviceid):
        sql = "select id, projectid, networkid from lampinfo where deviceid = '%s'" % deviceid
        address_info = {}
        try:
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) > 0:
                address_info = info_list[0]
                return True, address_info
            else:
                return False, address_info

        except:
            logger.error(traceback.format_exc())
            return False, address_info

    def get_network_info_by_networktype(self, networktype):
        sql = " "
        if networktype == LORA_WAN_TYPE:
            sql = "select id, projectid from network where protocoltype = %d and networkname = 'lorawan' limit 1" % networktype
        elif networktype == NBIOT_TYPE:
            sql = "select id, projectid from network where protocoltype = %d and networkname = 'NBIot' limit 1" % networktype
        address_info = {}
        try:
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) > 0:
                address_info = info_list[0]
                logger.error(str(address_info))
                return True, address_info
            else:
                return False, address_info

        except:
            logger.error(traceback.format_exc())
            return False,address_info

    def update_network_device_count(self, networkid):
        sql = "select lampcount from network where id = %d" % networkid
        lamp_info = {}
        try:
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) <= 0:
                return False
            lamp_info = info_list[0]

            lampcount = lamp_info["lampcount"]
            lampcount += 1

            lamp_sql = "update network set lampcount = %s where id = %s" % (lampcount, networkid)
            self.lamp_db.update(lamp_sql)
            return True
        except:
            logger.error(traceback.format_exc())
            return False

    def update_project_device_count(self, projectid):
        sql = "select lampcount from project where id = %d" % projectid
        lamp_info = {}
        try:
            info_list = self.lamp_db.getAll(sql)

            if len(info_list) <= 0:
                return False
            lamp_info = info_list[0]

            lampcount = lamp_info["lampcount"]
            lampcount += 1

            lamp_sql = "update project set lampcount = %s where id = %s" % (lampcount, projectid)
            self.lamp_db.update(lamp_sql)
            return True
        except:
            logger.error(traceback.format_exc())
            return False

    def create_new_lamp_for_lorawan(self, addr, neworkType):

        tblName = 'lampinfo'
        sql_head = "insert into " + tblName + "(number, projectid, networkid, status, address, createtime) values "
        values = ''
        reflag, network_info = self.get_network_info_by_networktype(neworkType)
        logger.error(str(network_info))

        sql = "select number from lampinfo ORDER BY number desc limit 1"

        info_list = self.lamp_db.getAll(sql)

        numberinfo = info_list[0] if len(info_list) > 0 else {}
        max_number = 0
        if len(numberinfo) > 0:
            max_number = int(numberinfo['number'])+1
        if reflag == False:
            return False
        values += "(" + str(max_number) + "," + str(network_info['projectid']) + "," + str(network_info['id']) + "," + "0,'" + str(addr) + "','" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        self.update_network_device_count(network_info['id'])
        self.update_project_device_count(network_info['projectid'])
        return True
    '''
    def create_new_lamp_nbiot(self, deviceid, devicesn, simid, neworkType):

        tblName = 'lampinfo'
        sql_head = "insert into " + tblName + "(number, projectid, networkid, status, deviceid, devicesn, simid, address, createtime) values "
        values = ''
        reflag, network_info = self.get_network_info_by_networktype(neworkType)
        logger.error(str(network_info))

        sql = "select number from lampinfo ORDER BY number desc limit 1"

        info_list = self.lamp_db.getAll(sql)

        numberinfo = info_list[0] if len(info_list) > 0 else {}
        max_number = 0
        if len(numberinfo) > 0:
            max_number = int(numberinfo['number'])+1
        if reflag == False:
            return False

        hexaddr = hex(int(devicesn))[2:]
        logger.error(str(hexaddr))
        values += "(" + str(max_number) + "," + str(network_info['projectid']) + "," + str(network_info['id']) + "," + "0,'" + str(deviceid) + "','" + str(devicesn) + "','" + str(simid) + "','" + hexaddr + "','" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
        if values.endswith(","):
            values  = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                self.lamp_db.insertOne(sql)
            except:
                logger.error(traceback.format_exc())
                return False
        self.update_network_device_count(network_info['id'])
        self.update_project_device_count(network_info['projectid'])

        return True
    '''

    def get_lampid_by_lampaddr(self,lampaddr):
        sql = "select id from lampinfo where address = '%08x'" % lampaddr
        lampid = 0
        try:
            info_list = self.lamp_db.getAll(sql)
            item = info_list[0] if len(info_list) > 0 else {}
            if len(item) > 0:
                lampid = item['id']
                return True, lampid
        except:
            logger.error(traceback.format_exc())
        return False, lampid


    def StringToInt(self,Src):
        return_val = 0
        for i,element in enumerate(Src):
            if element == 'A' or  element == 'a':
                return_val += 10*(16**(7-i))
            elif element == 'B' or  element == 'b':
                return_val += 11*(16**(7-i))
            elif element == 'C' or  element == 'c':
                return_val += 11*(16**(7-i))
            elif element == 'D' or  element == 'd':
                return_val += 13*(16**(7-i))
            elif element == 'E' or  element == 'e':
                return_val += 14*(16**(7-i))
            elif element == 'F' or  element == 'f':
                return_val += 15*(16**(7-i))
            else:
                return_val += int(element)*(16**(7-i))
        return return_val

    def get_patrol_rules(self,id = -1):
        current_time = dt_date_time.now()
        query_start_time = dt_date_time(current_time.year,current_time.month,current_time.day,current_time.hour,current_time.minute,0)
        if id != -1:
            sql = "select id,patroltype,patrolinterval,nextquerytime from patrol_cmd where id = {id}".format(id = id)
        else:
            #sql = "select id,patroltype,patrolinterval,nextquerytime from patrol_cmd where nextquerytime"\
            #      "< '%s' and id in (select id from lampinfo where networkid in (select id from network where status = 1)) limit 1000"%\
            #      (query_start_time.strftime("%Y-%m-%d %H:%M:%S"))

            sql = "select patrol_cmd.id,patrol_cmd.patroltype,patrol_cmd.patrolinterval,patrol_cmd.querycount,"\
                  "patrol_cmd.nextquerytime,network.status, network.protocoltype from patrol_cmd "\
                  "left join lampinfo on patrol_cmd.id = lampinfo.id "\
                  "left join network on lampinfo.networkid = network.id where patrol_cmd.patroltype = 1 and "\
                  "network.status = 1 and network.protocoltype = 0 and patrol_cmd.nextquerytime < '%s'"\
                  " order by nextquerytime asc;" % (query_start_time.strftime("%Y-%m-%d %H:%M:%S"))
        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        values_list = []
        for item in info_list:
            relateid = item['id']
            result, address = self.get_lamp_address_info(relateid)
            if not result:
                continue
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':16,
                'address':address,
            }
            ''' if 10 minutes can't updata, will try it again!'''
            if id == -1:
                #patrolinterval = item['patrolinterval']
                querycount = int(item['querycount'])
                if querycount < 3:
                    querycount += 1
                patrolinterval = querycount
                current_nextquerytime = query_start_time
                if (query_start_time - item['nextquerytime']) < timedelta(hours=patrolinterval):
                    current_nextquerytime = item['nextquerytime']
                nextquerytime = current_nextquerytime + timedelta(hours=patrolinterval)
                value = (nextquerytime.strftime("%Y-%m-%d %H:%M:%S"), querycount, item['id'])
                values_list.append(value)
            if item['patrolinterval'] > 0:
                cmd_list.append(cmd)
        if len(values_list) > 0:
            update_sql = "update patrol_cmd set nextquerytime = %s,querycount = %s where id = %s"
            try:
                self.lamp_db.updateMany(update_sql, values_list)
            except:
                logger.error(" update error!")

        logger.error("get_patrol_rules::get ruleinfo complete")
        return True, cmd_list

    def get_group_policy_cmd(self):
        current_time = dt_date_time.now()
        query_start_time = dt_date_time(current_time.year,current_time.month,current_time.day,current_time.hour,current_time.minute,0)

        sql = "select id, light, policyid from policy_cmd where nextquerytime"\
              "< '%s' and id in (select id from lampinfo where networkid in (select id from network where status = 1))"%\
              (query_start_time.strftime("%Y-%m-%d %H:%M:%S"))

        info_list = []
        try:
            info_list = self.lamp_db.getAll(sql)
        except:
            logger.error(traceback.format_exc())
            return False,[]
        cmd_list = []
        values_list = []
        for item in info_list:
            relateid = item['id']
            result, address = self.get_lamp_address_info(relateid)
            if not result:
                continue
            cmd ={
                'cmdtype':0,
                'relateid':item['id'],
                'code':16,
                'address':address,
            }
            ''' if 10 minutes can't updata, will try it again!'''
            if id == -1:
                patrolinterval = item['patrolinterval']
                current_nextquerytime = query_start_time
                if (query_start_time - item['nextquerytime']) < timedelta(hours = patrolinterval):
                    current_nextquerytime = item['nextquerytime']
                nextquerytime = current_nextquerytime + timedelta(hours = patrolinterval)
                value = (nextquerytime.strftime("%Y-%m-%d %H:%M:%S"),item['id'])
                values_list.append(value)
            if item['patrolinterval'] > 0 and item['patroltype'] > 0:
                cmd_list.append(cmd)
        if len(values_list) > 0:
            update_sql = "update patrol_cmd set nextquerytime = %s where id = %s"
            try:
                self.lamp_db.updateMany(update_sql, values_list)
            except:
                logger.error(" update error!")

        logger.error("get_patrol_rules::get ruleinfo complete")
        return True, cmd_list

    def patrol_rules_updatetime(self,id):
        current_time = dt_date_time.now()
        patrolinterval = 1
        '''
        try:
            sql = 'select patrolinterval from patrol_cmd where id = %d' % id
            info_list = self.lamp_db.getAll(sql)
            for item in info_list:
                patrolinterval = item['patrolinterval']
        except:
            logger.error(traceback.format_exc())
            return False
        '''
        nextquerytime = current_time + timedelta(hours = patrolinterval)
        update_sql = "update patrol_cmd set nextquerytime = '%s',querycount = 0 where id = %d " % (nextquerytime.strftime("%Y-%m-%d %H:%M:%S"),id)
        try:
            self.lamp_db.update(update_sql)
        except:
            logger.error(traceback.format_exc())
            return False
        return True


    def get_network_info(self,company,projectid ,networkid):
        sql = 'select net.hbcycle as heart_period,net.id as network_id, prj.id as prj_id from ' \
                '(select id from company where ename = "%s") company  '\
                'left join(select id,company from project where projectid = "%s") prj on prj.company = company.id'\
                ' left join (select id,projectid, hbcycle from network where networkid = "%s") net ' \
                'on net.projectid = prj.id' % (company,projectid,networkid)

        network_info ={"id":0,"prj_id":0,"heart_period":180}
        try:
            info_list = self.lamp_db.getAll(sql)
            for item in info_list:
                network_info['heart_period'] = item['heart_period']
                network_info['id'] = item['network_id']
                network_info['prj_id'] = item['prj_id']
                break
            #logger.info(" network_info['id'] %s length:%s" %  (network_info['id'],len(info_list)))
            return True,network_info
        except:
            logger.error(traceback.format_exc())
            return False,network_info

    def reset_network_status(self,network_ids):
        """
        reset network status
        if a new client connect, update network status and ip addr
        """
        if len(network_ids) == 0:
            try:
                sql = "update network set status = 0"
                self.lamp_db.update(sql)
            except:
                logger.error(traceback.format_exc())
            return

        values_list = []
        for net in network_ids:
            value = (net['status'],net['ip'], net['port'], net['network_id'])
            values_list.append(value)
        if len(values_list) > 0:
            networksql = "update network set status = %s, ip = %s, port = %s where id = %s"
            try:
                self.lamp_db.updateMany(networksql, values_list)
            except:
                logger.error(traceback.format_exc())

    def update_network_status(self, network_infos):
        try:
            values_lista = []
            values_listb = []
            devid_values_list = []
            for item in network_infos:
                if item.has_key('rssi') and item.has_key('snr'):
                    value = (item['rssi'], item['snr'], item['networkid'])
                    values_lista.append(value)
                elif item.has_key('rssi'):
                    value = (item['rssi'], item['networkid'])
                    values_listb.append(value)
                elif item.has_key('deviceid'):
                    value = (item['deviceid'], item['networkid'])
                    devid_values_list.append(value)

            if len(values_lista) > 0:
                logger.error("update %s " % str(values_lista))
                networksql = "update network set rssi = %s, snr = %s where id = %s"
                self.lamp_db.updateMany(networksql, values_lista)

            if len(values_listb) > 0:
                networksql = "update network set rssi = %s where id = %s"
                self.lamp_db.updateMany(networksql, values_listb)

            if len(devid_values_list) > 0:
                networksql = "update network set deviceid = %s where id = %s"
                self.lamp_db.updateMany(networksql, devid_values_list)

        except:
            logger.error(traceback.format_exc())

    def update_lamp_devstatus(self, lamp_infos):
        try:
            values_list = []
            for item in lamp_infos:
                value = (item['devstatus'], item['lampid'])
                values_list.append(value)

            if len(values_list) > 0:
                lampinfosql = "update lampinfo set devstatus = %s where id = %s"
                self.lamp_db.updateMany(lampinfosql, values_list)

        except:
            logger.error(traceback.format_exc())

    def reset_update_status(self):
        '''
        initialize all update status
        :return:
        '''
        try:
            sql = "update batch_update_cmd set statuscmd = 1"
            self.lamp_db.update(sql)
        except:
            logger.error(traceback)

    def get_user_info(self, username):
        sql = "select companyid, zone, role from user where username = '%s'" % (username)
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
            #logger.info(str(info_list))
            return info_list
        except:
            logger.error(traceback.format_exc())
            return info_list

    def get_project_id_lists(self, companyid):
        sql = "select id from project where company = %s" % (companyid)
        info_list = []

        try:
            info_list = self.lamp_db.getAll(sql)
            #logger.info(str(info_list))
            return info_list
        except:
            logger.error(traceback.format_exc())
            return info_list

    def update_lampstatus(self, lampid, value):
        lamp_sql = ''
        try:
            lampinfosql = "select id from lamp_info_log where lampid = %s order by id desc limit 1" % lampid
            info_list = self.lamp_db.getAll(lampinfosql)
            item = info_list[0] if len(info_list) > 0 else {}

            if len(item) > 0:
                if value == 0:
                    lamp_sql = "update lamp_info_log set lampstatus = 0, lighteness = 0 where id = %s" % item['id']
                else:
                    lamp_sql = "update lamp_info_log set lampstatus = 1, lighteness = 100 where id = %s" % item['id']
                self.lamp_db.update(lamp_sql)

            if value == 0:
                lamp_sql = "update lampinfo set status = 0, lighteness = 0 where id = %s" % lampid
            else:
                lamp_sql = "update lampinfo set status = 1, lighteness = 100 where id = %s" % lampid
            self.lamp_db.update(lamp_sql)
        except:
            logger.error(traceback)

    def update_lighteness(self, lampid, value):

        try:
            lampinfosql = "select id from lamp_info_log where lampid = %s order by id desc limit 1" % lampid
            info_list = self.lamp_db.getAll(lampinfosql)
            item = info_list[0] if len(info_list) > 0 else {}
            if len(item) > 0:
                if value == 0:
                    lamp_sql = "update lamp_info_log set lighteness = %s, lampstatus = 0 where id = %s" % (
                    value, item['id'])
                else:
                    lamp_sql = "update lamp_info_log set lighteness = %s, lampstatus = 1 where id = %s" % (
                    value, item['id'])
                self.lamp_db.update(lamp_sql)
            if value == 0:
                lamp_sql = "update lampinfo set lighteness = %s, status = 0 where id = %s" % (value, lampid)
            else:
                lamp_sql = "update lampinfo set lighteness = %s, status = 1 where id = %s" % (value, lampid)
            self.lamp_db.update(lamp_sql)
        except:
            logger.error(traceback)

    def update_electric_supply_status(self, id, value):

        try:
            lampinfosql = "select * from lamp_info_log where lampid = %s order by id desc limit 1" %id
            info_list = self.lamp_db.getAll(lampinfosql)
            #logger.info(str(info_list))
            if len(info_list) > 0:
                item = info_list[0]
                item["electric_status"] = value
                item["updatetime"] = dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S')
                sql_head = "insert into lamp_info_log (lampid, lighteness, lampstatus, lampvoltage, lampcurrent, lamppower, temper, electric_status, updatetime) values "
                values = "(" + str(item['lampid']) + "," + str(item['lighteness']) + "," +str(item['lampstatus'])+ "," \
                    + str(item['lampvoltage'])+ "," + str(item['lampcurrent'])+ "," \
                    + str(item['lamppower']) + "," + str(item['temper']) + "," + str(item['electric_status']) + ", '" + item["updatetime"] + "')"
                #logger.info(sql_head+values)
                self.lamp_db.insertOne(sql_head+values)

        except:
            logger.error(traceback)

    def get_battery_setting_info(self, id, data_info):

        try:
            sql_head = "replace into battery_cmd (id, capacity, vovervoltage," \
                "vlimitedcharge, vbalancecharge, vpromotecharge, vfloatingcharge," \
                "vpromoterecover, voverdischargerecover, vundervoltagewarn, voverdischarge," \
                "balancechargetime, promotechargetime, balanceinterval, tempcompensation," \
                "tempcompmax, tempcompmin, turnfloatingcurrent, batterytype, vsystem, minusnocharge, chargemode, updatetime) values "
            #logger.info(sql_head)
            values = "(" + str(id) + "," +\
            str(data_info['capacity']) + \
            "," + str(data_info['vovervoltage'])+\
            "," + str(data_info['vlimitedcharge'])+\
            "," + str(data_info['vbalancecharge'])+\
            "," + str(data_info['vpromotecharge'])+\
            "," + str(data_info['vfloatingcharge'])+\
            "," + str(data_info['vpromoterecover'])+\
            "," + str(data_info['voverdischargerecover'])+\
            "," + str(data_info['vundervoltagewarn'])+\
            "," + str(data_info['voverdischarge'])+\
            "," + str(data_info['balancechargetime'])+\
            "," + str(data_info['promotechargetime'])+\
            "," + str(data_info['balanceinterval'])+\
            "," + str(data_info['tempcompensation'])+\
            "," + str(data_info['tempcompmax'])+\
            "," + str(data_info['tempcompmin'])+\
            "," + str(data_info['turnfloatingcurrent'])+\
            "," + str(data_info['batterytype'])+\
            "," + str(data_info['vsystem'])+\
            "," + str(data_info['minusnocharge'])+\
            "," + str(data_info['chargemode'])+\
            ",'" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "')"
            update_sql = sql_head+values
            #logger.info(update_sql)
            self.lamp_db.update(update_sql)

        except:
            logger.error(traceback)

    def get_load_setting_info(self, id, data_info):

        try:
            sql_head = "replace into load_param_cmd (id,workmode,worktimefirst,"\
                "workpowerfirst,worktimesencond,workpowersencond,worktimethird,workpowerthird,"\
                "worktimeforth,workpowerforth,voptically,delaytime,ledloadcurrent,powercmd,switchfeature,updatetime) values "

            values = "(" + str(id) +\
            "," + str(data_info['workmode']) + \
            "," + str(data_info['worktimefirst']) + \
            "," + str(data_info['workpowerfirst']) + \
            "," + str(data_info['worktimesencond']) + \
            "," + str(data_info['workpowersencond']) + \
            "," + str(data_info['worktimethird']) + \
            "," + str(data_info['workpowerthird']) + \
            "," + str(data_info['worktimeforth']) + \
            "," + str(data_info['workpowerforth']) + \
            "," + str(data_info['voptically']) + \
            "," + str(data_info['delaytime']) + \
            "," + str(data_info['ledloadcurrent']) + \
            "," + str(data_info['powercmd']) + \
            "," + str(data_info['switchfeature']) + \
            ",'" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "')"
            #logger.info(values)
            self.lamp_db.update(sql_head+values)

        except:
            logger.error(traceback)

    def insert_env_monitor_log(self, info_list):
        try:
            if len(info_list) == 0:
                return True

            env_monitor_sql = "insert into envmonitor_info_log (envmonitorid, humidity, temperature, " \
                              "noise, PM25, " \
                              "PM10, atmospressue, updatetime) values (%s, %s, %s, %s, " \
                              "%s, %s, %s, %s);"

            params = []
            for item in info_list:
                params.append([item['devid'], item['humidity'], item['temperature'], item['noise'],
                               item['PM25'], item['PM10'], item['atmospressue'], item['time']])

            ret = self.lamp_db.insertMany(env_monitor_sql, params)
            return True

        except:
            logger.error(traceback.format_exc())
            return False

    def save_env_monitor_log(self, result_list):

        envmonitor_info_list = []

        for item in result_list:
            if type(item['devid']) != long:
                logger.error(item['devid'])
                continue
            public_elment = {"devid": item['devid'],
                             "time": item['time']}

            envmonitor_info = item['envmonitorInfo']
            envmonitor_info.update(public_elment)
            envmonitor_info_list.append(envmonitor_info)

        if not self.insert_env_monitor_log(envmonitor_info_list):
            self.insert_env_monitor_log(envmonitor_info_list)


    def get_patrol_time_cmd(self, pdu, id):
        cmd_list = []

        sql = "select lampinfo.networkid,lampinfo.address, network.protocoltype from lampinfo, network " \
              "where lampinfo.id = %s and lampinfo.networkid = network.id" % id
        lampinfo_list = self.lamp_db.getAll(sql)
        lampcmditem = lampinfo_list[0] if len(lampinfo_list) > 0 else {}
        if lampcmditem:
            cmd = {
                'cmdtype': 10,
                'protocoltype': lampcmditem['protocoltype'],
                'networkid': lampcmditem['networkid'],
                'address': lampcmditem['address'],
                'code': 0,
                'pdu': pdu,
                'value': 0}
            cmd_list.append(cmd)
            return True, cmd_list
        return False, []

    def set_patrol_time_cmd(self, pdu, id):
        sql = "select patroltype,patrolinterval from patrol_cmd where id = %s" % id
        try:
            info_list = self.lamp_db.getAll(sql)

            cmd_list = []
            cmditem = info_list[0] if len(info_list) > 0 else {}
            if cmditem:
                interval = cmditem['patrolinterval'] if cmditem['patrolinterval'] else 1
                sql = "select lampinfo.networkid,lampinfo.address, network.protocoltype from lampinfo, network " \
                "where lampinfo.id = %s and lampinfo.networkid = network.id" % id
                lampinfo_list = self.lamp_db.getAll(sql)
                lampcmditem = lampinfo_list[0] if len(lampinfo_list) > 0 else {}
                if lampcmditem:
                    cmd = {
                        'cmdtype': 10,
                        'protocoltype': lampcmditem['protocoltype'],
                        'networkid': lampcmditem['networkid'],
                        'address': lampcmditem['address'],
                        'code': 0,
                        'pdu': pdu,
                        'value': int(interval)}
                    cmd_list.append(cmd)
                    return True, cmd_list
            return False, []
        except:
            logger.error(sql)
            logger.error(traceback.format_exc())
            return False, []

    def get_server_address_cmd(self, pdu, id):
        pass

    def set_server_address_cmd(self, pdu, id):
        pass